Telegram Group & Telegram Channel
🧠 Уровень Pro: Медиана, ранги и NULL в Oracle SQL

📋 Есть таблица sales:


CREATE TABLE sales (
salesman_id NUMBER,
region VARCHAR2(50),
amount NUMBER
);


📦 Данные:

| salesman_id | region | amount |
|-------------|------------|--------|
| 101 | 'North' | 200 |
| 101 | 'North' | NULL |
| 102 | 'North' | 150 |
| 103 | 'North' | NULL |
| 104 | 'South' | 300 |
| 105 | 'South' | NULL |
| 106 | 'South' | 50 |
| 107 | 'South' | NULL |


🎯 Задача 2.0:
Вывести salesman_id, чья сумма продаж меньше медианы по региону,
и показать ранг продавца внутри региона по сумме продаж, где NULL = 0.

Подвохи:
- MEDIAN() доступен только в Oracle.
- Нужно предварительно агрегировать суммы.
- Продавцы с только NULL-продажами = 0.
- Ранг должен учитывать правильную сортировку и связи с регионом.

Решение:

```sql
WITH sales_total AS (
SELECT
salesman_id,
region,
NVL(SUM(amount), 0) AS total_sales
FROM sales
GROUP BY salesman_id, region
),
region_median AS (
SELECT
region,
MEDIAN(total_sales) AS region_median
FROM sales_total
GROUP BY region
),
ranked AS (
SELECT
st.salesman_id,
st.region,
st.total_sales,
r.region_median,
RANK() OVER (PARTITION BY st.region ORDER BY st.total_sales DESC) AS sales_rank
FROM sales_total st
JOIN region_median r ON st.region = r.region
)
SELECT *
FROM ranked
WHERE total_sales < region_median;
```

🧠 Объяснение:

1. `sales_total`: агрегируем продажи по продавцу, `NULL → 0`
2. `region_median`: считаем **медиану** продаж по каждому региону
3. `ranked`: добавляем `RANK()` по убыванию продаж внутри региона
4. Финальный фильтр: продажи ниже медианы

🔍 Пример вывода:

| salesman_id | region | total_sales | region_median | sales_rank |
|-------------|--------|-------------|----------------|-------------|
| 105 | South | 0 | 50 | 3 |
| 107 | South | 0 | 50 | 3 |
| 103 | North | 0 | 150 | 3 |

📌 Польза:

Отлично проверяет:
- знание оконных функций
- работу с медианой
- поведение `NULL` в агрегатах
- построение CTE-цепочек и аналитики

🔁 Можно расширить:
- Добавить ранги *по убыванию и по возрастанию*
- Вместо `MEDIAN()` использовать `PERCENTILE_CONT()`
- Построить дэшборд: кто всегда "ниже медианы" за месяц

@sqlhub



tg-me.com/sqlhub/1888
Create:
Last Update:

🧠 Уровень Pro: Медиана, ранги и NULL в Oracle SQL

📋 Есть таблица sales:


CREATE TABLE sales (
salesman_id NUMBER,
region VARCHAR2(50),
amount NUMBER
);


📦 Данные:

| salesman_id | region | amount |
|-------------|------------|--------|
| 101 | 'North' | 200 |
| 101 | 'North' | NULL |
| 102 | 'North' | 150 |
| 103 | 'North' | NULL |
| 104 | 'South' | 300 |
| 105 | 'South' | NULL |
| 106 | 'South' | 50 |
| 107 | 'South' | NULL |


🎯 Задача 2.0:
Вывести salesman_id, чья сумма продаж меньше медианы по региону,
и показать ранг продавца внутри региона по сумме продаж, где NULL = 0.

Подвохи:
- MEDIAN() доступен только в Oracle.
- Нужно предварительно агрегировать суммы.
- Продавцы с только NULL-продажами = 0.
- Ранг должен учитывать правильную сортировку и связи с регионом.

Решение:

```sql
WITH sales_total AS (
SELECT
salesman_id,
region,
NVL(SUM(amount), 0) AS total_sales
FROM sales
GROUP BY salesman_id, region
),
region_median AS (
SELECT
region,
MEDIAN(total_sales) AS region_median
FROM sales_total
GROUP BY region
),
ranked AS (
SELECT
st.salesman_id,
st.region,
st.total_sales,
r.region_median,
RANK() OVER (PARTITION BY st.region ORDER BY st.total_sales DESC) AS sales_rank
FROM sales_total st
JOIN region_median r ON st.region = r.region
)
SELECT *
FROM ranked
WHERE total_sales < region_median;
```

🧠 Объяснение:

1. `sales_total`: агрегируем продажи по продавцу, `NULL → 0`
2. `region_median`: считаем **медиану** продаж по каждому региону
3. `ranked`: добавляем `RANK()` по убыванию продаж внутри региона
4. Финальный фильтр: продажи ниже медианы

🔍 Пример вывода:

| salesman_id | region | total_sales | region_median | sales_rank |
|-------------|--------|-------------|----------------|-------------|
| 105 | South | 0 | 50 | 3 |
| 107 | South | 0 | 50 | 3 |
| 103 | North | 0 | 150 | 3 |

📌 Польза:

Отлично проверяет:
- знание оконных функций
- работу с медианой
- поведение `NULL` в агрегатах
- построение CTE-цепочек и аналитики

🔁 Можно расширить:
- Добавить ранги *по убыванию и по возрастанию*
- Вместо `MEDIAN()` использовать `PERCENTILE_CONT()`
- Построить дэшборд: кто всегда "ниже медианы" за месяц

@sqlhub

BY Data Science. SQL hub


Warning: Undefined variable $i in /var/www/tg-me/post.php on line 283

Share with your friend now:
tg-me.com/sqlhub/1888

View MORE
Open in Telegram


Data Science SQL hub Telegram | DID YOU KNOW?

Date: |

Telegram hopes to raise $1bn with a convertible bond private placement

The super secure UAE-based Telegram messenger service, developed by Russian-born software icon Pavel Durov, is looking to raise $1bn through a bond placement to a limited number of investors from Russia, Europe, Asia and the Middle East, the Kommersant daily reported citing unnamed sources on February 18, 2021.The issue reportedly comprises exchange bonds that could be converted into equity in the messaging service that is currently 100% owned by Durov and his brother Nikolai.Kommersant reports that the price of the conversion would be at a 10% discount to a potential IPO should it happen within five years.The minimum bond placement is said to be set at $50mn, but could be lowered to $10mn. Five-year bonds could carry an annual coupon of 7-8%.

How Does Telegram Make Money?

Telegram is a free app and runs on donations. According to a blog on the telegram: We believe in fast and secure messaging that is also 100% free. Pavel Durov, who shares our vision, supplied Telegram with a generous donation, so we have quite enough money for the time being. If Telegram runs out, we will introduce non-essential paid options to support the infrastructure and finance developer salaries. But making profits will never be an end-goal for Telegram.

Data Science SQL hub from it


Telegram Data Science. SQL hub
FROM USA